package com.ruoyi.system.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.dao.UserInfoDao;
import com.ruoyi.system.mapper.SysHolderAccountMapper;

@Service
public class UserInfoDaoImpl implements UserInfoDao {

	@Autowired
	private JdbcTemplate primaryJdbcTemplate;
	
	@Autowired
	private SysHolderAccountMapper sysHolderAccountMapper;

	@Override
	public List<Map<String, Object>> getUserInfoList(String jobNo) {

		String sql = "select 'OA' as systemName,EMPNAME as userName,EMPNUMBER as jobNo,USERACCOUNTS as userAccount,latest_logon_time as lastLogonTime,case when USERISACTIVE=0 then '禁用' when USERISACTIVE=1 then '启用' else '' end as userStatus from Portal_OA用户帐号清单 where EMPNUMBER='"
				+ jobNo + "' "
				+ " UNION ALL select 'JY' as systemName,EMPNAME as userName,EMPNUMBER as jobNo,USERACCOUNTS as userAccount,cast(nullif('','') as datetime) as lastLogonTime,case when USERISACTIVE=1 then '启用' when USERISACTIVE=0 then '禁用' else '' end as userStatus from Portal_经营用户帐号清单 where EMPNUMBER='"
				+ jobNo + "'";

		List<Map<String, Object>> userInfoList = primaryJdbcTemplate.queryForList(sql);
		return userInfoList;
	}

	@Override
	public List<String> getAuthUserInfo(String jobNo) {

		String sql = "select 'OA' from Portal_OA用户帐号清单 where EMPNUMBER='" + jobNo + "'AND USERISACTIVE ='1' "
				+ " UNION ALL select 'JY' from Portal_经营用户帐号清单 where EMPNUMBER='" + jobNo + "'AND USERISACTIVE='1'";

		List<String> stringList = primaryJdbcTemplate.queryForList(sql, String.class);
		return stringList;
	}
	
	@Override
	public List<Map<String, Object>> getUserByAccountAndFlag(String account,String flag) {

		String sql = "";
		if("OA".equals(flag)) {
			sql = "select '"+flag+"' as systemName,EMPNAME as userName,EMPNUMBER as jobNo,USERACCOUNTS as userAccount,CONVERT(varchar(100), latest_logon_time, 20) as lastLogonTime,case when USERISACTIVE=0 then '禁用' when USERISACTIVE=1 then '启用' else '' end as userStatus from Portal_OA用户帐号清单 where USERACCOUNTS='"
				+ account + "' ";
		}
		if("JY".equals(flag)) {
			sql = "select '"+flag+"' as systemName,EMPNAME as userName,EMPNUMBER as jobNo,USERACCOUNTS as userAccount,cast(nullif('','') as datetime) as lastLogonTime,case when USERISACTIVE=1 then '启用' when USERISACTIVE=0 then '禁用' else '' end as userStatus from Portal_经营用户帐号清单 where USERACCOUNTS='"
					+ account + "'";
		}

		List<Map<String, Object>> stringList = primaryJdbcTemplate.queryForList(sql);
		return stringList;
	}

	@Override
	public List<Map<String, Object>> OAUserListPage(Map<String, String> reqMap) {
		int pageSize = Integer.parseInt(reqMap.get("pageSize"));
		int pageIndex = Integer.parseInt(reqMap.get("pageIndex"));
		int total = pageSize * (pageIndex - 1);
		String sql = "select top " + pageSize
				+ " a.EMPNAME as userName,a.EMPNUMBER as userNo,a.EMPIDCARD as userIdCard,a.USERACCOUNTS as userAccounts,"
				+ " a.USERISACTIVE as userIsActive,a.ORGNAME as orgName,a.user_is_sleep as userIsSleep,CONVERT(varchar(100), a.latest_logon_time, 20) as loginDate from Portal_OA用户帐号清单"
				+ " a where a.EMPNUMBER not in (select top " + total + " b.EMPNUMBER from Portal_OA用户帐号清单 b where 1=1 ";
		if (StringUtils.isNotBlank(reqMap.get("userName"))) {
			sql = sql + " and b.EMPNAME like ('%" + reqMap.get("userName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userNo"))) {
			sql = sql + " and b.EMPNUMBER like ('%" + reqMap.get("userNo") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIdCard"))) {
			sql = sql + " and b.EMPIDCARD like ('%" + reqMap.get("userIdCard") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userAccounts"))) {
			sql = sql + " and b.USERACCOUNTS like ('%" + reqMap.get("userAccounts") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsActive"))) {
			sql = sql + " and b.USERISACTIVE = " + reqMap.get("userIsActive");
		}
		if (StringUtils.isNotBlank(reqMap.get("orgName"))) {
			sql = sql + " and b.ORGNAME like ('%" + reqMap.get("orgName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsSleep"))) {
			sql = sql + " and b.user_is_sleep = " + reqMap.get("userIsSleep");
		}
		/*
		 * if(StringUtils.isNotBlank(reqMap.get("loginDate"))) {
		 * 
		 * }
		 */
		sql = sql + " order by ";
		if (StringUtils.isNotBlank(reqMap.get("sortBy"))) {
			if ("userName".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.EMPNAME ";
			} else if ("userNo".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.EMPNUMBER ";
			} else if ("userIdCard".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.EMPIDCARD ";
			} else if ("userAccounts".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.USERACCOUNTS ";
			} else if ("userIsActive".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.USERISACTIVE ";
			} else if ("orgName".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.ORGNAME ";
			} else if ("userIsSleep".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.user_is_sleep ";
			} else if ("loginDate".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.latest_logon_time ";
			} else {
				sql = sql + " b.EMPNAME ";
			}
		} else {
			sql = sql + " b.EMPNUMBER ";
		}

		if (StringUtils.isNotBlank(reqMap.get("order"))) {
			sql = sql + " " + reqMap.get("order") + " )";
		} else {
			sql = sql + " asc)";
		}
		if (StringUtils.isNotBlank(reqMap.get("userName"))) {
			sql = sql + " and a.EMPNAME like ('%" + reqMap.get("userName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userNo"))) {
			sql = sql + " and a.EMPNUMBER like ('%" + reqMap.get("userNo") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIdCard"))) {
			sql = sql + " and a.EMPIDCARD like ('%" + reqMap.get("userIdCard") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userAccounts"))) {
			sql = sql + " and a.USERACCOUNTS like ('%" + reqMap.get("userAccounts") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsActive"))) {
			sql = sql + " and a.USERISACTIVE = " + reqMap.get("userIsActive");
		}
		if (StringUtils.isNotBlank(reqMap.get("orgName"))) {
			sql = sql + " and a.ORGNAME like ('%" + reqMap.get("orgName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsSleep"))) {
			sql = sql + " and a.user_is_sleep = " + reqMap.get("userIsSleep");
		}
		/*
		 * if(StringUtils.isNotBlank(reqMap.get("loginDate"))) {
		 * 
		 * }
		 */
		sql = sql + " order by ";
		if (StringUtils.isNotBlank(reqMap.get("sortBy"))) {
			if ("userName".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.EMPNAME ";
			} else if ("userNo".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.EMPNUMBER ";
			} else if ("userIdCard".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.EMPIDCARD ";
			} else if ("userAccounts".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.USERACCOUNTS ";
			} else if ("userIsActive".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.USERISACTIVE ";
			} else if ("orgName".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.ORGNAME ";
			} else if ("userIsSleep".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.user_is_sleep ";
			} else if ("loginDate".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.latest_logon_time ";
			} else {
				sql = sql + " a.EMPNAME ";
			}
		} else {
			sql = sql + " a.EMPNUMBER ";
		}

		if (StringUtils.isNotBlank(reqMap.get("order"))) {
			sql = sql + " " + reqMap.get("order");
		} else {
			sql = sql + " asc";
		}

		List<Map<String, Object>> resList = primaryJdbcTemplate.queryForList(sql);
		if(resList!=null && resList.size()>0) {
			for (Map<String, Object> map : resList) {
				String res = sysHolderAccountMapper.selectHolderNamesOA(map.get("userAccounts").toString());
				map.put("holderNames", res);
			}
		}
		return resList;
	}

	@Override
	public List<Map<String, Object>> JYUserListPage(Map<String, String> reqMap) {
		int pageSize = Integer.parseInt(reqMap.get("pageSize"));
		int pageIndex = Integer.parseInt(reqMap.get("pageIndex"));
		int total = pageSize * (pageIndex - 1);
		// String sql = "select top "+pageSize+" a.* from Portal_经营用户帐号清单 a where
		// a.EMPNUMBER not in (select top "+total+" b.EMPNUMBER from Portal_经营用户帐号清单 b
		// where 1=1 order by b.EMPNUMBER asc ) where 1=1 ORDER BY a.EMPNUMBER asc";

		String sql = "select top " + pageSize
				+ " a.EMPNAME as userName,a.EMPNUMBER as userNo,a.EMPIDCARD as userIdCard,a.USERACCOUNTS as userAccounts,"
				+ " a.USERISACTIVE as userIsActive,a.ORGNAME as orgName,a.USERISDELETED as userIsDeleted from Portal_经营用户帐号清单"
				+ " a where a.EMPNUMBER not in (select top " + total + " b.EMPNUMBER from Portal_经营用户帐号清单 b where 1=1 ";
		if (StringUtils.isNotBlank(reqMap.get("userName"))) {
			sql = sql + " and b.EMPNAME like ('%" + reqMap.get("userName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userNo"))) {
			sql = sql + " and b.EMPNUMBER like ('%" + reqMap.get("userNo") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIdCard"))) {
			sql = sql + " and b.EMPIDCARD like ('%" + reqMap.get("userIdCard") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userAccounts"))) {
			sql = sql + " and b.USERACCOUNTS like ('%" + reqMap.get("userAccounts") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsActive"))) {
			sql = sql + " and b.USERISACTIVE = " + reqMap.get("userIsActive");
		}
		if (StringUtils.isNotBlank(reqMap.get("orgName"))) {
			sql = sql + " and b.ORGNAME like ('%" + reqMap.get("orgName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsDeleted"))) {
			sql = sql + " and b.USERISDELETED = " + reqMap.get("userIsDeleted");
		}
		sql = sql + " order by ";
		if (StringUtils.isNotBlank(reqMap.get("sortBy"))) {
			if ("userName".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.EMPNAME ";
			} else if ("userNo".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.EMPNUMBER ";
			} else if ("userIdCard".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.EMPIDCARD ";
			} else if ("userAccounts".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.USERACCOUNTS ";
			} else if ("userIsActive".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.USERISACTIVE ";
			} else if ("orgName".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.ORGNAME ";
			} else if ("userIsDeleted".equals(reqMap.get("sortBy"))) {
				sql = sql + " b.USERISDELETED ";
			} else {
				sql = sql + " b.EMPNAME ";
			}
		} else {
			sql = sql + " b.EMPNUMBER ";
		}

		if (StringUtils.isNotBlank(reqMap.get("order"))) {
			sql = sql + " " + reqMap.get("order") + " )";
		} else {
			sql = sql + " asc)";
		}
		if (StringUtils.isNotBlank(reqMap.get("userName"))) {
			sql = sql + " and a.EMPNAME like ('%" + reqMap.get("userName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userNo"))) {
			sql = sql + " and a.EMPNUMBER like ('%" + reqMap.get("userNo") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIdCard"))) {
			sql = sql + " and a.EMPIDCARD like ('%" + reqMap.get("userIdCard") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userAccounts"))) {
			sql = sql + " and a.USERACCOUNTS like ('%" + reqMap.get("userAccounts") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsActive"))) {
			sql = sql + " and a.USERISACTIVE = " + reqMap.get("userIsActive");
		}
		if (StringUtils.isNotBlank(reqMap.get("orgName"))) {
			sql = sql + " and a.ORGNAME like ('%" + reqMap.get("orgName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsDeleted"))) {
			sql = sql + " and a.USERISDELETED = " + reqMap.get("userIsDeleted");
		}

		sql = sql + " order by ";
		if (StringUtils.isNotBlank(reqMap.get("sortBy"))) {
			if ("userName".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.EMPNAME ";
			} else if ("userNo".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.EMPNUMBER ";
			} else if ("userIdCard".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.EMPIDCARD ";
			} else if ("userAccounts".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.USERACCOUNTS ";
			} else if ("userIsActive".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.USERISACTIVE ";
			} else if ("orgName".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.ORGNAME ";
			} else if ("userIsDeleted".equals(reqMap.get("sortBy"))) {
				sql = sql + " a.USERISDELETED ";
			} else {
				sql = sql + " a.EMPNAME ";
			}
		} else {
			sql = sql + " a.EMPNUMBER ";
		}

		if (StringUtils.isNotBlank(reqMap.get("order"))) {
			sql = sql + " " + reqMap.get("order");
		} else {
			sql = sql + " asc";
		}

		List<Map<String, Object>> resList = primaryJdbcTemplate.queryForList(sql);
		if(resList!=null && resList.size()>0) {
			for (Map<String, Object> map : resList) {
				String res = sysHolderAccountMapper.selectHolderNamesJY(map.get("userAccounts").toString());
				map.put("holderNames", res);
			}
		}
		return resList;
	}

	@Override
	public int OAUserTotal(Map<String, String> reqMap) {
		String sql = "select count(*) from Portal_OA用户帐号清单 b where 1=1 ";
		
		if (StringUtils.isNotBlank(reqMap.get("userName"))) {
			sql = sql + " and b.EMPNAME like ('%" + reqMap.get("userName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userNo"))) {
			sql = sql + " and b.EMPNUMBER like ('%" + reqMap.get("userNo") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIdCard"))) {
			sql = sql + " and b.EMPIDCARD like ('%" + reqMap.get("userIdCard") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userAccounts"))) {
			sql = sql + " and b.USERACCOUNTS like ('%" + reqMap.get("userAccounts") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsActive"))) {
			sql = sql + " and b.USERISACTIVE = " + reqMap.get("userIsActive");
		}
		if (StringUtils.isNotBlank(reqMap.get("orgName"))) {
			sql = sql + " and b.ORGNAME like ('%" + reqMap.get("orgName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsSleep"))) {
			sql = sql + " and b.user_is_sleep = " + reqMap.get("userIsSleep");
		}

		Integer res = primaryJdbcTemplate.queryForObject(sql, Integer.class);
		return res;
	}

	@Override
	public int JYUserTotal(Map<String, String> reqMap) {
		String sql = "select count(*) from Portal_经营用户帐号清单 b where 1=1 ";
		if (StringUtils.isNotBlank(reqMap.get("userName"))) {
			sql = sql + " and b.EMPNAME like ('%" + reqMap.get("userName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userNo"))) {
			sql = sql + " and b.EMPNUMBER like ('%" + reqMap.get("userNo") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIdCard"))) {
			sql = sql + " and b.EMPIDCARD like ('%" + reqMap.get("userIdCard") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userAccounts"))) {
			sql = sql + " and b.USERACCOUNTS like ('%" + reqMap.get("userAccounts") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsActive"))) {
			sql = sql + " and b.USERISACTIVE = " + reqMap.get("userIsActive");
		}
		if (StringUtils.isNotBlank(reqMap.get("orgName"))) {
			sql = sql + " and b.ORGNAME like ('%" + reqMap.get("orgName") + "%')";
		}
		if (StringUtils.isNotBlank(reqMap.get("userIsDeleted"))) {
			sql = sql + " and b.USERISDELETED = " + reqMap.get("userIsDeleted");
		}
		Integer res = primaryJdbcTemplate.queryForObject(sql, Integer.class);
		return res;
	}

	@Override
	public List<Map<String, Object>> getAllOAUser() {
		String sql = "select EMP_ID as guid,EMPNAME as userName,EMPNUMBER as userNo,EMPIDCARD as idCard,USERACCOUNTS as loginName,latest_logon_time as lastLogonTime,USERISACTIVE as isActive,ORGNAME as orgName,user_is_sleep as isSleep,MDM_ID as mdmId from Portal_OA用户帐号清单 ";
		List<Map<String, Object>> userInfoList = primaryJdbcTemplate.queryForList(sql);
		return userInfoList;
	}

	@Override
	public List<Map<String, Object>> getAllJYUser() {
		String sql = "select EMP_ID as guid,EMPNAME as userName,EMPNUMBER as userNo,EMPIDCARD as idCard,USERACCOUNTS as loginName,u_isadmin as isadmin,USERISACTIVE as isActive,ORGNAME as orgName,USERISDELETED as isdeleted,MDM_ID as mdmId from Portal_经营用户帐号清单 ";
		List<Map<String, Object>> userInfoList = primaryJdbcTemplate.queryForList(sql);
		return userInfoList;
	}

}
